package com.red.wood.common.springjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.red.wood.common.util.CommonUtils;
import com.red.wood.common.util.IMap;

@Repository
public class CommonDao {
	
	private static final Log log = LogFactory.getLog(CommonDao.class);
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	/**
	 * @param params
	 * @param sqlSb StringBuilder sqlSb = new StringBuilder("insert into tableName (");
	 * @return
	 */
	public String insertTableData(IMap params, StringBuilder sqlSb) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		StringBuilder valueStr = new StringBuilder(") values (");
		List<Object> paramList = new ArrayList<Object>();
		if (params != null && !params.isEmpty()) {
			for (Entry<String, Object> entry : params.entrySet()) {
				if (StringUtils.isNoneBlank(CommonUtils.valueOf(entry.getValue()))) {
					sqlSb.append(entry.getKey() + ",");
					valueStr.append("?,");
					paramList.add(entry.getValue());
				}
			}
		}
		String sql = sqlSb.substring(0, sqlSb.length() - 1) + valueStr.substring(0, valueStr.length() - 1) + ")";
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int n = 0; n < paramList.size(); n++) {
					ps.setObject(n + 1, paramList.get(n));
				}
				return ps;
			}
		}, keyHolder);
		return String.valueOf(keyHolder.getKey().longValue());
	}
	
	/**
	 * 
	 * @param params
	 * @param sqlSb StringBuilder sql = new StringBuilder("update tableName set ");
	 * @param typeId
	 * @param id
	 * @return
	 */
	public int updateTableData(IMap params, StringBuilder sqlSb, String typeId, String id) {
		int n = 0;
		if (StringUtils.isNotEmpty(id)) {
			List<Object> paramList = new ArrayList<Object>();
			if (params != null && !params.isEmpty()) {
				for (Entry<String, Object> entry : params.entrySet()) {
					if (StringUtils.isNotBlank(CommonUtils.valueOf(entry.getValue()))) {
						sqlSb.append(entry.getKey() + "=?,");
						paramList.add(entry.getValue());
					}
				}
			}

			String str = sqlSb.substring(0, sqlSb.length() - 1) + " where " + typeId + " in (" + id + ")";
			n = jdbcTemplate.update(str, new PreparedStatementSetter() {
				public void setValues(PreparedStatement ps) throws SQLException {
					for (int n = 0; n < paramList.size(); n++) {
						ps.setObject(n + 1, paramList.get(n));
					}
				}
			});
		}
		return n;
	}

	/**
	 * @param params
	 * @param sqlSb StringBuilder sql = new StringBuilder("update tableName set ");
	 * @param queryConf where id = 4971 and ackCode = 0
	 * @return
	 */
	public int updateTableData(IMap params, StringBuilder sqlSb,String queryConf) {
		int n = 0;
		List<Object> paramList = new ArrayList<Object>();
		if (params != null && !params.isEmpty()) {
			for (Entry<String, Object> entry : params.entrySet()) {
				if (StringUtils.isNotBlank(CommonUtils.valueOf(entry.getValue()))) {
					sqlSb.append(entry.getKey() + "=?,");
					paramList.add(entry.getValue());
				}
			}
		}
		String str = sqlSb.substring(0, sqlSb.length() - 1) + queryConf;
		n = jdbcTemplate.update(str, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				for (int n = 0; n < paramList.size(); n++) {
					ps.setObject(n + 1, paramList.get(n));
				}
			}
		});
		return n;
	}
	
	/**
	 * StringBuilder sqlSb = new StringBuilder("select * from table where 1 = 1");
	 * @param params
	 * @param sqlSb
	 * @return
	 */
	public List<Map<String, Object>> queryTableData(IMap params, StringBuilder sqlSb) {
		List<Object> paramList = new ArrayList<Object>();
		if (params != null && !params.isEmpty()) {
			for (Entry<String, Object> entry : params.entrySet()) {
				if (StringUtils.isNotBlank(CommonUtils.valueOf(entry.getValue()))) {
					sqlSb.append(" and " + entry.getKey() + " = ?");
					paramList.add(entry.getValue());
				}
			}
		}
		String sql = sqlSb.toString();
		return jdbcTemplate.queryForList(sql, paramList.toArray());
	}

	/**
	 * 返回Bean集合
	 * @param params
	 * @param sqlSb StringBuilder sql = new StringBuilder("select * from tableName where 1 = 1");
	 * @param classType Bean.class
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryTableDataBeanList(IMap params, StringBuilder sqlSb,Class<?> classType) {
		RowMapper<T> rw =  (RowMapper<T>) ParameterizedBeanPropertyRowMapper.newInstance(classType);
		List<Object> paramList = new ArrayList<Object>();
		if (params != null && !params.isEmpty()) {
			for (Entry<String, Object> entry : params.entrySet()) {
				if (StringUtils.isNotBlank(CommonUtils.valueOf(entry.getValue()))) {
					sqlSb.append(" and " + entry.getKey() + " = ?");
					paramList.add(entry.getValue());
				}
			}
		}
		String sql = sqlSb.toString();
		return (List<T>) jdbcTemplate.query(sql, paramList.toArray(),rw);
	}
	
	/**
	 * 返回Bean
	 * @param params
	 * @param sqlSb StringBuilder sql = new StringBuilder("select * from tableName where 1 = 1");
	 * @param classType Bean.class
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> T queryTableDataBean(IMap params, StringBuilder sqlSb,Class<?> classType) {
		RowMapper<T> rw =  (RowMapper<T>) ParameterizedBeanPropertyRowMapper.newInstance(classType);
		List<Object> paramList = new ArrayList<Object>();
		if (params != null && !params.isEmpty()) {
			for (Entry<String, Object> entry : params.entrySet()) {
				if (StringUtils.isNotBlank(CommonUtils.valueOf(entry.getValue()))) {
					sqlSb.append(" and " + entry.getKey() + " = ?");
					paramList.add(entry.getValue());
				}
			}
		}
		String sql = sqlSb.toString();
		return (T) jdbcTemplate.queryForObject(sql, paramList.toArray(),rw);
	}
	
	public int deleteTableData(String sql) {
		return jdbcTemplate.update(sql);
	}
	
	/**
	 * 此种方式批量插入只适合少量数据插入，插入性能很差
	 * @param list
	 * @param sql : insert into tbl_device(mainDevice_id,device_type,device_id,device_ip,device_port) values (?,?,?,?,?)
	 * @param columns : mainDevice_id,device_type,device_id,device_ip,device_port
	 */
	public void insertBatchTableData(List<Map<String, Object>> list,String sql, String columns) {
		String[] columnsSplit = columns.split(","); 
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i){
				try{
					Map<String, Object> map = list.get(i);
					for (int j = 0; j < map.size(); j++) {
						ps.setObject(j + 1, map.get(columnsSplit[j]));
					}
				}catch(Exception e){
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
			public int getBatchSize() {
				return list.size();
			}
		});
	}
	
	/**
	 * 此种方式批量插入性能很快，适合大数据量插入,批量插入建议用此种方式
	 * @param list
	 * @param sql : insert into tbl_device(mainDevice_id,device_type,device_id,device_ip,device_port) values (?,?,?,?,?)
	 * @param columns : mainDevice_id,device_type,device_id,device_ip,device_port
	 * @param size
	 */
	public void insertBatchTableData(List<Map<String, Object>> list,String sql, String columns,long size) {
		String[] columnsSplit = columns.split(","); 
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			DataSource dataSource = jdbcTemplate.getDataSource();
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			int n = 0;
			for (int i = 0; i < list.size(); i++) {
				Map<String, Object> map = list.get(i);
				for (int j = 0; j < map.size(); j++) {
					ps.setObject(j + 1, map.get(columnsSplit[j]));
				}
				ps.addBatch();
				if (++n % size == 0) {
					ps.executeBatch();
					conn.commit();
				}
			}
			ps.executeBatch();
			conn.commit();
		}catch(Exception e){
			if(log.isErrorEnabled()){
				log.error(e.getMessage(),e);
			}
		}finally{
			if(ps != null){
				try {
					ps.close();
				} catch (SQLException e) {
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
		}
	}
	
	/**
	 * 通过jdbc插入返回自增主键ID
	 * @param map
	 * @param sql : insert into tbl_device(mainDevice_id,device_type,device_id,device_ip,device_port) values (?,?,?,?,?)
	 * @param columns : mainDevice_id,device_type,device_id,device_ip,device_port
	 * @return
	 */
	public long getInsertTableDataForJdbcPrimaryKey(Map<String, Object> map,String sql, String columns) {
		String[] columnsSplit = columns.split(","); 
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		long id = 0;
		try {
			DataSource dataSource = jdbcTemplate.getDataSource();
			conn = dataSource.getConnection();
			ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			conn.setAutoCommit(false);
			for (int j = 0; j < map.size(); j++) {
				ps.setObject(j + 1, map.get(columnsSplit[j]));
			}
			ps.executeUpdate();
			conn.commit();
			rs = ps.getGeneratedKeys();
			if(rs.next()){
				id = rs.getLong(1);
			}
		}catch(Exception e){
			if(log.isErrorEnabled()){
				log.error(e.getMessage(),e);
			}
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
			if(ps != null){
				try {
					ps.close();
				} catch (SQLException e) {
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
			if(conn != null){
				try {
					conn.close();
				} catch (SQLException e) {
					if(log.isErrorEnabled()){
						log.error(e.getMessage(),e);
					}
				}
			}
		}
		return id;
	}
}
